set hive.exec.dynamic.partition = true;
set hive.exec.dynamic.partition.mode = 'nonstrict';
set hive.exec.max.dynamic.partitions = 200;
set hive.exec.max.dynamic.partitions.pernode = 200;

insert overwrite table jms_dm.dm_thd_door_pick_up_detail_dt
select
     network_code                --网点编码
    ,network_name                --网点名称
    ,city_code                   --城市编码
    ,city_name                   --城市名称
    ,provider_code               --省份编码
    ,provider_name               --省份名称
    ,agent_code                  --代理区编码
    ,agent_name                  --代理区名称
    ,virt_code                   --虚拟代理区编码
    ,virt_name                   --虚拟代理区名称
    ,network_zone_code           --网管片区编码
    ,network_zone_name           --网管片区名称
    ,area_zone_code              --区域片区编码
    ,area_zone_name              --区域片区名称
    ,order_num                   --总单量
    ,recept_num                  --接单量
    ,pick_num                    --取件量
    ,cancel_num                  --取消量
    ,overtime_pick_num           --超时取件量
    ,timely_pick_num             --取件及时量
    ,break_num                   --爽约量
    ,courier_change_num          --快递员改约量
    ,change_other_num            --改约到其他日期量
    ,timely_pick_per             --取件及时率 ==
    ,cancel_per                  --取消率
    ,break_per                   --爽约率 ==
    ,courier_change_per          --快递员改约率 ==
    ,complaint_num               --投诉量
    ,complaint_again_num         --二次投诉量
    ,complaint_per               --投诉率 ==
    ,send_back_num               --自行寄回单量 ==
    ,phone_conn_per              --电联率  ==
    ,pun_phone_conn_per          --准时电联率 ==
    ,should_collect_num          --应揽收量
    ,timely_collect_num          --揽收及时量
    ,timely_collect_per          --揽收及时率
    ,sign_num                    --签收量
    ,sign_per                    --签收率 ==
    ,pricing_num                 --核价量
    ,timely_pricing_num          --核价及时量
    ,timely_pricing_per          --核价及时率
    ,wd_complaint                --万单投诉率
    ,hj_fine_amt                 --核价罚款
    ,qx_fine_amt                 --取消率罚款
    ,wdts_fine_amt               --万单投诉率罚款
    ,lsbjs_fine_amt              --揽收及时率罚款
    ,fine_amt                    --总罚款
    ,dt
from (
    select
         base.*
        ,base.hj_fine_amt + base.qx_fine_amt  + base.wdts_fine_amt + base.lsbjs_fine_amt as fine_amt  --总罚款
        ,code          as network_code       --网点编码
        ,name          as network_name       --网点名称
        ,city_id       as city_code          --城市编码
        ,city_desc     as city_name          --城市名称
        ,provider_id   as provider_code      --省份编码
        ,provider_desc as provider_name      --省份名称
        ,agent_code    as agent_code         --代理区编码
        ,agent_name    as agent_name         --代理区名称
        ,virt_code     as virt_code          --虚拟代理区编码
        ,virt_name     as virt_name          --虚拟代理区名称
        ,dim.area_code as network_zone_code  --网管片区编码
        ,dim.area_name as network_zone_name  --网管片区名称
        ,dim.zone_code as area_zone_code     --区域片区编码
        ,dim.zone_name as area_zone_name     --区域片区名称
    from (
        select
             organ_code                  --组织编码
            ,organ_name                  --组织名称
            ,province                    --省
            ,city                        --市
            ,order_num                   --总单量
            ,recept_num                  --接单量
            ,pick_num                    --取件量
            ,cancel_num                  --取消量
            ,overtime_pick_num           --超时取件量
            ,timely_pick_num             --取件及时量
            ,break_num                   --爽约量
            ,courier_change_num          --快递员改约量
            ,change_other_num            --改约到其他日期量
            ,timely_pick_per             --取件及时率
            ,cancel_per                  --取消率
            ,break_per                   --爽约率
            ,courier_change_per          --快递员改约率
            ,complaint_num               --投诉量
            ,complaint_again_num         --二次投诉量
            ,complaint_per               --投诉率
            ,send_back_num               --自行寄回单量
            ,phone_conn_per              --电联率
            ,pun_phone_conn_per          --准时电联率
            ,should_collect_num          --应揽收量
            ,timely_collect_num          --揽收及时量
            ,timely_collect_per          --揽收及时率
            ,sign_num                    --签收量
            ,sign_per                    --签收率
            ,pricing_num                 --核价量
            ,timely_pricing_num          --核价及时量
            ,timely_pricing_per          --核价及时率
            ,wd_complaint                --万单投诉
            ,case when hj_fine_amt    >= 200 then 200 else hj_fine_amt    end as hj_fine_amt    --核价罚款
            ,case when qx_fine_amt    >= 200 then 200 else qx_fine_amt    end as qx_fine_amt    --取消率罚款
            ,case when wdts_fine_amt  >= 200 then 200 else wdts_fine_amt  end as wdts_fine_amt  --万单投诉率罚款
            ,case when lsbjs_fine_amt >= 200 then 200 else lsbjs_fine_amt end as lsbjs_fine_amt --揽收及时率罚款
            ,dt
        from (
            select
                 organ_code                  --组织编码
                ,organ_name                  --组织名称
                ,province                    --省
                ,city                        --市
                ,order_num                   --总单量
                ,recept_num                  --接单量
                ,pick_num                    --取件量
                ,cancel_num                  --取消量
                ,overtime_pick_num           --超时取件量
                ,timely_pick_num             --取件及时量
                ,break_num                   --爽约量
                ,courier_change_num          --快递员改约量
                ,change_other_num            --改约到其他日期量
                ,timely_pick_per             --取件及时率
                ,cancel_per                  --取消率
                ,break_per                   --爽约率
                ,courier_change_per          --快递员改约率
                ,complaint_num               --投诉量
                ,complaint_again_num         --二次投诉量
                ,complaint_per               --投诉率
                ,send_back_num               --自行寄回单量
                ,phone_conn_per              --电联率
                ,pun_phone_conn_per          --准时电联率
                ,should_collect_num          --应揽收量
                ,timely_collect_num          --揽收及时量
                ,timely_collect_per          --揽收及时率
                ,sign_num                    --签收量
                ,sign_per                    --签收率
                ,pricing_num                 --核价量
                ,timely_pricing_num          --核价及时量
                ,timely_pricing_per          --核价及时率
                ,wd_complaint                --万单投诉
                ,case when order_num > 50 and timely_pricing_per < 95 then (pricing_num - timely_pricing_num) * 2 else 0 end as hj_fine_amt --核价罚款
                ,case when order_num > 50 and cancel_per > 5.6 then cancel_num * 2 else 0 end as qx_fine_amt --取消率罚款
                ,case when wd_complaint > 4.5 then complaint_num * 10 else 0 end as wdts_fine_amt --万单投诉率罚款
                ,case when order_num > 50 and timely_collect_per < 93 then (should_collect_num - timely_collect_num) * 2 else 0 end as lsbjs_fine_amt --揽收及时率罚款
                ,dt
            from jms_dwd.dwd_tab_door_pick_up_base_dt
            where dt = '{{ execution_date | cst_ds }}'
            and data_type = '1' --网点维度
        ) a
    ) base
    left join jms_dim.dim_network_whole_massage dim
       on dim.code = base.organ_code
) a
distribute by dt,pmod(hash(rand()),1);







insert overwrite table jms_dm.dm_thd_door_pick_up_day_dt
select
     serach_type           --查询类型
    ,site_code             --网点编码
    ,site_name             --网点名称
    ,agent_code            --代理区编码
    ,agent_name            --代理区名称
    ,virt_code             --虚拟代理区编码
    ,virt_name             --虚拟代理区名称
    ,order_num             --总单量
    ,recept_num            --接单量
    ,pick_num              --取件量
    ,cancel_num            --取消量
    ,overtime_pick_num     --超时取件量
    ,timely_pick_num       --取件及时量
    ,break_num             --爽约量
    ,courier_change_num    --快递员改约量
    ,change_other_num      --改约到其他日期量
    ,complaint_num         --投诉量
    ,complaint_again_num   --二次投诉量
    ,send_back_num         --自行寄回单量
    ,should_collect_num    --应揽收量
    ,timely_collect_num    --揽收及时量
    ,sign_num              --签收量
    ,pricing_num           --核价量
    ,timely_pricing_num    --核价及时量
    ,hj_fine_amt           --核价罚款
    ,qx_fine_amt           --取消率罚款
    ,wdts_fine_amt         --万单投诉率罚款
    ,lsbjs_fine_amt        --揽收及时率罚款
    ,fine_amt              --总罚款
    ,timely_pick_rate      --取件及时率     --完成值
    ,cancel_rate           --取消率         --完成值
    ,break_rate            --爽约率         --完成值
    ,courier_change_rate   --快递员改约率   --完成值
    ,complaint_rate        --投诉率         --完成值
    ,phone_conn_rate       --电联率         --完成值
    ,pun_phone_conn_rate   --准时电联率     --完成值
    ,timely_collect_rate   --揽收及时率     --完成值
    ,sign_rate             --签收率         --完成值
    ,timely_pricing_rate   --核价及时率     --完成值
    ,wd_complaint_rate     --万单投诉率     --完成值
    ,round(hj_score  ,2) as hj_score   --核价及时率得分
    ,round(ls_score  ,2) as ls_score   --揽收及时率得分
    ,round(qx_score  ,2) as qx_score   --取消率得分
    ,round(wdts_score,2) as wdts_score --万单投诉率得分
    ,round(ts_score  ,2) as ts_score   --投诉量得分
    ,round((hj_score + ls_score + qx_score +  wdts_score),0) as total_score --总得分
    ,case when round((hj_score + ls_score + qx_score +  wdts_score),0) > 90 then 1 else 0 end as if_reach --是否当日达标
    ,dt
from (
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '网点'                   as serach_type        --查询类型
            ,network_code             as site_code          --网点编码
            ,network_name             as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_detail_dt
        where dt =  '{{ execution_date | cst_ds }}'
        group by network_code  --网点编码
                ,network_name  --网点名称
                ,agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
                ,dt
    ) a

    union all
    --代理区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '代理区'                 as serach_type        --查询类型
            ,agent_code               as site_code          --网点编码
            ,agent_name               as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_detail_dt
        where dt =  '{{ execution_date | cst_ds }}'
        group by agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
                ,dt
    ) a
    union all
    --虚拟代理区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '虚拟代理区'            as serach_type        --查询类型
            ,virt_code               as site_code          --网点编码
            ,virt_name               as site_name          --网点名称
            ,null                    as agent_code         --代理区编码
            ,null                    as agent_name         --代理区名称
            ,virt_code               as virt_code          --虚拟代理区编码
            ,virt_name               as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_detail_dt
        where dt =  '{{ execution_date | cst_ds }}'
        group by virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
                ,dt
    ) a

    union all
    --网管片区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '网管片区'               as serach_type        --查询类型
            ,network_zone_code        as site_code          --网点编码
            ,network_zone_name        as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_detail_dt
        where dt =  '{{ execution_date | cst_ds }}'
        group by network_zone_code --网管片区编码
                ,network_zone_name --网管片区名称
                ,agent_code        --代理区编码
                ,agent_name        --代理区名称
                ,virt_code         --虚拟代理区编码
                ,virt_name         --虚拟代理区名称
                ,dt
    ) a
    union all
    --市场片区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,dt
    from (
        select
             '市场片区'               as serach_type        --查询类型
            ,area_zone_code           as site_code          --网点编码
            ,area_zone_name           as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,dt
        from jms_dm.dm_thd_door_pick_up_detail_dt
        where dt =  '{{ execution_date | cst_ds }}'
        group by area_zone_code    --区域片区编码
                ,area_zone_name    --区域片区名称
                ,agent_code        --代理区编码
                ,agent_name        --代理区名称
                ,virt_code         --虚拟代理区编码
                ,virt_name         --虚拟代理区名称
                ,dt
    ) a
) a
distribute by dt,pmod(hash(rand()),1);




insert overwrite table jms_dm.dm_thd_door_pick_up_month_dt
select
     serach_type           --查询类型
    ,site_code             --网点编码
    ,site_name             --网点名称
    ,agent_code            --代理区编码
    ,agent_name            --代理区名称
    ,virt_code             --虚拟代理区编码
    ,virt_name             --虚拟代理区名称
    ,order_num             --总单量
    ,recept_num            --接单量
    ,pick_num              --取件量
    ,cancel_num            --取消量
    ,overtime_pick_num     --超时取件量
    ,timely_pick_num       --取件及时量
    ,break_num             --爽约量
    ,courier_change_num    --快递员改约量
    ,change_other_num      --改约到其他日期量
    ,complaint_num         --投诉量
    ,complaint_again_num   --二次投诉量
    ,send_back_num         --自行寄回单量
    ,should_collect_num    --应揽收量
    ,timely_collect_num    --揽收及时量
    ,sign_num              --签收量
    ,pricing_num           --核价量
    ,timely_pricing_num    --核价及时量
    ,hj_fine_amt           --核价罚款
    ,qx_fine_amt           --取消率罚款
    ,wdts_fine_amt         --万单投诉率罚款
    ,lsbjs_fine_amt        --揽收及时率罚款
    ,fine_amt              --总罚款
    ,timely_pick_rate      --取件及时率     --完成值
    ,cancel_rate           --取消率         --完成值
    ,break_rate            --爽约率         --完成值
    ,courier_change_rate   --快递员改约率   --完成值
    ,complaint_rate        --投诉率         --完成值
    ,phone_conn_rate       --电联率         --完成值
    ,pun_phone_conn_rate   --准时电联率     --完成值
    ,timely_collect_rate   --揽收及时率     --完成值
    ,sign_rate             --签收率         --完成值
    ,timely_pricing_rate   --核价及时率     --完成值
    ,wd_complaint_rate     --万单投诉率     --完成值
    ,round(hj_score  ,2) as hj_score   --核价及时率得分
    ,round(ls_score  ,2) as ls_score   --揽收及时率得分
    ,round(qx_score  ,2) as qx_score   --取消率得分
    ,round(wdts_score,2) as wdts_score --万单投诉率得分
    ,round(ts_score  ,2) as ts_score   --投诉量得分
    ,round((hj_score + ls_score + qx_score + case when wdts_score > ts_score then wdts_score else ts_score end ),0) as total_score --总得分
    ,total_not_reach_cnt --累积不达标次数
    ,dt
from (
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,total_not_reach_cnt --累积不达标次数
        ,dt
    from (
        select
             '网点'                   as serach_type        --查询类型
            ,site_code                as site_code          --网点编码
            ,site_name                as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,sum(case when if_reach = 0 then 1 else 0 end) as total_not_reach_cnt --累积不达标次数
            ,'{{ execution_date | cst_ds }}' as dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}' --月初至统计日
        and serach_type = '网点'
        group by site_code     --网点编码
                ,site_name     --网点名称
                ,agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
    ) a

    union all
    --代理区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,total_not_reach_cnt --累积不达标次数
        ,dt
    from (
        select
             '代理区'                 as serach_type        --查询类型
            ,site_code                as site_code          --网点编码
            ,site_name                as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,sum(case when if_reach = 0 then 1 else 0 end) as total_not_reach_cnt --累积不达标次数
            ,'{{ execution_date | cst_ds }}' as dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}' --月初至统计日
        and serach_type = '代理区'
        group by site_code     --网点编码
                ,site_name     --网点名称
                ,agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
    ) a
     union all
    --虚拟代理区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,total_not_reach_cnt --累积不达标次数
        ,dt
    from (
        select
             '虚拟代理区'            as serach_type        --查询类型
            ,site_code               as site_code          --网点编码
            ,site_name               as site_name          --网点名称
            ,agent_code              as agent_code         --代理区编码
            ,agent_name              as agent_name         --代理区名称
            ,virt_code               as virt_code          --虚拟代理区编码
            ,virt_name               as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,sum(case when if_reach = 0 then 1 else 0 end) as total_not_reach_cnt --累积不达标次数
            ,'{{ execution_date | cst_ds }}' as dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt = '{{ execution_date | cst_ds }}'
        and serach_type = '虚拟代理区'
        group by site_code     --网点编码
                ,site_name     --网点名称
                ,agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
    ) a

    union all
    --网管片区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,total_not_reach_cnt --累积不达标次数
        ,dt
    from (
        select
             '网管片区'               as serach_type        --查询类型
            ,site_code                as site_code          --网点编码
            ,site_name                as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,sum(case when if_reach = 0 then 1 else 0 end) as total_not_reach_cnt --累积不达标次数
            ,'{{ execution_date | cst_ds }}' as dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}' --月初至统计日
        and serach_type = '网管片区'
        group by site_code     --网点编码
                ,site_name     --网点名称
                ,agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
    ) a
    union all
    --市场片区
    select
         serach_type           --查询类型
        ,site_code             --网点编码
        ,site_name             --网点名称
        ,agent_code            --代理区编码
        ,agent_name            --代理区名称
        ,virt_code             --虚拟代理区编码
        ,virt_name             --虚拟代理区名称
        ,order_num             --总单量
        ,recept_num            --接单量
        ,pick_num              --取件量
        ,cancel_num            --取消量
        ,overtime_pick_num     --超时取件量
        ,timely_pick_num       --取件及时量
        ,break_num             --爽约量
        ,courier_change_num    --快递员改约量
        ,change_other_num      --改约到其他日期量
        ,complaint_num         --投诉量
        ,complaint_again_num   --二次投诉量
        ,send_back_num         --自行寄回单量
        ,should_collect_num    --应揽收量
        ,timely_collect_num    --揽收及时量
        ,sign_num              --签收量
        ,pricing_num           --核价量
        ,timely_pricing_num    --核价及时量
        ,hj_fine_amt           --核价罚款
        ,qx_fine_amt           --取消率罚款
        ,wdts_fine_amt         --万单投诉率罚款
        ,lsbjs_fine_amt        --揽收及时率罚款
        ,fine_amt              --总罚款
        ,timely_pick_rate      --取件及时率     --完成值
        ,cancel_rate           --取消率         --完成值
        ,break_rate            --爽约率         --完成值
        ,courier_change_rate   --快递员改约率   --完成值
        ,complaint_rate        --投诉率         --完成值
        ,phone_conn_rate       --电联率         --完成值
        ,pun_phone_conn_rate   --准时电联率     --完成值
        ,timely_collect_rate   --揽收及时率     --完成值
        ,sign_rate             --签收率         --完成值
        ,timely_pricing_rate   --核价及时率     --完成值
        ,wd_complaint_rate     --万单投诉率     --完成值
        ,case when timely_pricing_rate >= 98 and timely_pricing_rate <= 100 then 20
              when timely_pricing_rate >= 90 and timely_pricing_rate <  98  then (timely_pricing_rate - 90) / (98 - 90) * (20 - 15) + 15
              when timely_pricing_rate >= 76 and timely_pricing_rate <  90  then (timely_pricing_rate - 76) / (90 - 76) * (15 - 10) + 10
              when timely_pricing_rate >= 60 and timely_pricing_rate <  76  then (timely_pricing_rate - 60) / (76 - 60) * (10 - 4 ) + 4
              when timely_pricing_rate >= 50 and timely_pricing_rate <  60  then (timely_pricing_rate - 50) / (60 - 50) * (4  - 0 ) + 0
              when timely_pricing_rate >= 0  and timely_pricing_rate <  50  then 0
              else 0 end as hj_score    --核价及时率得分
        ,case when timely_collect_rate >= 98 and timely_collect_rate <= 100 then 25
              when timely_collect_rate >= 90 and timely_collect_rate <  98  then (timely_collect_rate - 90) / (98 - 90) * (25 - 22) + 22
              when timely_collect_rate >= 76 and timely_collect_rate <  90  then (timely_collect_rate - 76) / (90 - 76) * (22 - 16) + 16
              when timely_collect_rate >= 58 and timely_collect_rate <  76  then (timely_collect_rate - 58) / (76 - 58) * (16 - 4 ) + 4
              when timely_collect_rate >= 48 and timely_collect_rate <  58  then (timely_collect_rate - 48) / (58 - 48) * (4  - 0 ) + 0
              when timely_collect_rate >= 0  and timely_collect_rate <  48  then 0
              else 0 end as ls_score --揽收及时率得分
        ,case when cancel_rate > 20  and cancel_rate <= 100 then 0
              when cancel_rate > 14  and cancel_rate <= 20  then (cancel_rate - 14) / (20 - 14) * (0  - 4 ) + 4
              when cancel_rate > 10  and cancel_rate <= 14  then (cancel_rate - 10) / (14 - 10) * (4  - 16) + 16
              when cancel_rate > 6   and cancel_rate <= 10  then (cancel_rate - 6 ) / (10 - 6 ) * (16 - 22) + 22
              when cancel_rate > 4   and cancel_rate <= 6   then (cancel_rate - 4 ) / (6  - 4 ) * (22 - 25) + 25
              when cancel_rate >= 0  and cancel_rate <= 4   then 25
              else 0 end as qx_score --取消率得分
        ,case when wd_complaint_rate > 16                                 then 0
              when wd_complaint_rate > 11.2 and wd_complaint_rate <= 16   then (wd_complaint_rate - 11.2) / (16   - 11.2) * (0  - 8 ) + 8
              when wd_complaint_rate > 6.2  and wd_complaint_rate <= 11.2 then (wd_complaint_rate - 6.2 ) / (11.2 - 6.2 ) * (8  - 18) + 18
              when wd_complaint_rate > 4.5  and wd_complaint_rate <= 6.2  then (wd_complaint_rate - 4.5 ) / (6.2  - 4.5 ) * (18 - 25) + 25
              when wd_complaint_rate > 3.2  and wd_complaint_rate <= 4.5  then (wd_complaint_rate - 3.2 ) / (4.5  - 3.2 ) * (25 - 30) + 30
              when wd_complaint_rate >= 0   and wd_complaint_rate <= 3.2  then 30
              else 0 end as wdts_score  --万单投诉率得分
        ,case when complaint_num = 0 then 30
              when complaint_num = 1 then 25
              when complaint_num = 2 then 23
              when complaint_num = 3 then 20
              when complaint_num = 4 then 16
              when complaint_num = 5 then 12
              when complaint_num = 6 then 8
              when complaint_num > 6 then 0
              else 0 end as ts_score --投诉量得分
        ,total_not_reach_cnt --累积不达标次数
        ,dt
    from (
        select
             '市场片区'               as serach_type        --查询类型
            ,site_code                as site_code          --网点编码
            ,site_name                as site_name          --网点名称
            ,agent_code               as agent_code         --代理区编码
            ,agent_name               as agent_name         --代理区名称
            ,virt_code                as virt_code          --虚拟代理区编码
            ,virt_name                as virt_name          --虚拟代理区名称
            ,sum(order_num          ) as order_num          --总单量
            ,sum(recept_num         ) as recept_num         --接单量
            ,sum(pick_num           ) as pick_num           --取件量
            ,sum(cancel_num         ) as cancel_num         --取消量
            ,sum(overtime_pick_num  ) as overtime_pick_num  --超时取件量
            ,sum(timely_pick_num    ) as timely_pick_num    --取件及时量
            ,sum(break_num          ) as break_num          --爽约量
            ,sum(courier_change_num ) as courier_change_num --快递员改约量
            ,sum(change_other_num   ) as change_other_num   --改约到其他日期量
            ,sum(complaint_num      ) as complaint_num      --投诉量
            ,sum(complaint_again_num) as complaint_again_num--二次投诉量
            ,sum(send_back_num      ) as send_back_num      --自行寄回单量
            ,sum(should_collect_num ) as should_collect_num --应揽收量
            ,sum(timely_collect_num ) as timely_collect_num --揽收及时量
            ,sum(sign_num           ) as sign_num           --签收量
            ,sum(pricing_num        ) as pricing_num        --核价量
            ,sum(timely_pricing_num ) as timely_pricing_num --核价及时量
            ,sum(hj_fine_amt        ) as hj_fine_amt        --核价罚款
            ,sum(qx_fine_amt        ) as qx_fine_amt        --取消率罚款
            ,sum(wdts_fine_amt      ) as wdts_fine_amt      --万单投诉率罚款
            ,sum(lsbjs_fine_amt     ) as lsbjs_fine_amt     --揽收及时率罚款
            ,sum(fine_amt           ) as fine_amt           --总罚款
            ,case when sum(order_num) = 0 then 100 else sum(timely_pick_num   ) / sum(order_num) * 100 end as timely_pick_rate    --取件及时率
            ,case when sum(order_num) = 0 then 0   else sum(cancel_num        ) / sum(order_num) * 100 end as cancel_rate         --取消率
            ,case when sum(order_num) = 0 then 0   else sum(break_num         ) / sum(order_num) * 100 end as break_rate          --爽约率
            ,case when sum(order_num) = 0 then 0   else sum(courier_change_num) / sum(order_num) * 100 end as courier_change_rate --快递员改约率
            ,case when sum(order_num) = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 100 end as complaint_rate      --投诉率
            ,null as phone_conn_rate       --电联率
            ,null as pun_phone_conn_rate   --准时电联率
            ,case when sum(should_collect_num) = 0 then 100 else sum(timely_collect_num) / sum(should_collect_num) * 100 end as timely_collect_rate   --揽收及时率
            ,case when sum(order_num         ) = 0 then 100 else sum(sign_num          ) / sum(order_num         ) * 100 end as sign_rate             --签收率
            ,case when sum(pricing_num       ) = 0 then 100 else sum(timely_pricing_num) / sum(pricing_num       ) * 100 end as timely_pricing_rate   --核价及时率
            ,case when sum(order_num) * 10000  = 0 then 0   else sum(complaint_num     ) / sum(order_num) * 10000  end as wd_complaint_rate     --万单投诉率
            ,sum(case when if_reach = 0 then 1 else 0 end) as total_not_reach_cnt --累积不达标次数
            ,'{{ execution_date | cst_ds }}' as dt
        from jms_dm.dm_thd_door_pick_up_day_dt
        where dt between trunc('{{ execution_date | cst_ds }}','MM') and '{{ execution_date | cst_ds }}' --月初至统计日
        and serach_type = '市场片区'
        group by site_code     --网点编码
                ,site_name     --网点名称
                ,agent_code    --代理区编码
                ,agent_name    --代理区名称
                ,virt_code     --虚拟代理区编码
                ,virt_name     --虚拟代理区名称
    ) a
) a
distribute by dt,pmod(hash(rand()),5);

